package com.trkj.dao.jpa;


import java.math.BigDecimal;
import java.util.List;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import com.trkj.pojos.Tbpower;

public interface IHwcPowerDAO extends CrudRepository<Tbpower, Long> {
	
	/**
	 * 根据员工的编号查询权限
	 * @param staffNo
	 * @return
	 */
	@Query("select p from Tbpower p where p.powerNo in (select sp.powersNo from Tbstaffpower sp where sp.staffNo = ?1)")
	  public List<Tbpower> queryStaffNoByPowers(BigDecimal staffNo);
	/**
	 * 根据员工的编号模糊查询权限
	 * @param staffNo
	 * @return
	 */
	@Query("select p from Tbpower p where p.powerNo in (select sp.powersNo from Tbstaffpower sp where sp.staffNo = ?1) and p.powerGrade like %?2%")
	public List<Tbpower> queryStaffNoByPowerGrade(BigDecimal staffNo,String powerGrade);
	/**
	 * 根据员工编号查询不属于该员工的权限
	 * @param staffNo
	 * @return
	 */
	//@Query("select p from Tbpower p where p.powerNo  not in(select sp.powersNo from Tbstaffpowers sp where sp.staffNo = ?1)")
	//@Query("select p from Tbpower p where p.powerNo in (select sp.powersNo from Tbstaffpower sp where sp.staffNo = ?1)")
	@Query("select p from Tbpower p where p.powerNo not in (select sp.powersNo from Tbstaffpower sp where sp.staffNo = ?1)")
	public List<Tbpower>queryStaffNoNotPowers(BigDecimal staffNo);
	
	/**
	 * 根据员工编号以及字符模糊查询不属于该员工的权限
	 * @param staffNo
	 * @param powerGrade
	 * @return
	 */
	@Query("select p from Tbpower p where p.powerNo not in (select sp.powersNo from Tbstaffpower sp where sp.staffNo = ?1) and p.powerGrade like %?2%")
	public List<Tbpower> queryNotStaffNoNOtPowers(BigDecimal staffNo,String powerGrade);
	/**
	 * 根据传入的编号查询权限编号
	 * @param staffNo
	 * @return
	 */
	@Query("select p from Tbpower p where p.powerNo  in (select sp.powersNo from Tbstaffpower sp where sp.staffNo = ?1)")
	public List<Tbpower> findAccountBystaffPowers(BigDecimal staffNo);
	
}
